    #!/bin/perl
package data;
    use strict;
    use DBI;
    use utf8;

	#这一句，可以避免出现由于变量只使用一次出现的 use only once 警告 
	no warnings('once');  
	#包含配置文件
	require '../conf/config.pl';
    
    our %db_conf = %conf::db_conf ; 
    my $db_host = $db_conf{'db_host'};
    my $db_user = $db_conf{'db_user'};
    my $db_pwd = $db_conf{'db_pwd'};
    
    # Connect to target DB
    my $dbh = DBI->connect("$db_host","$db_user","$db_pwd", {'RaiseError' => 1,'mysql_enable_utf8'=>1});

    #my $sqr = $dbh->prepare("SELECT t.customer_id as customer,t.title as subject,u.first_name as engineer,t.create_time as time  FROM ticket t,users u where t.user_id=u.id and t.queue_id<>3 order by t.create_time desc ");

    my $contents="<style type='text/css'>div{border:0px solid #CCC;} div.h{float:left;width:80px;margin:auto;overflow-x:hidden;} body {background-color:#D5D5D5} table.header {font-size:13pt;background-color:#F3F3F3; width:80%; height:8;border-top: 2px solid #FFFFFF; border-left: 2px solid #FFFFFF; border-right: 2px solid #8995FA; border-bottom: 2px solid #8995FA; } table.rate {font-size:16pt;background-color:#F3F3F3;width:80% } table.maincontent {font-size:11pt;background-color:#F3F3F3;width:80%;border-collapse:collapse;  } table.maincontent td{border:solid #000 1px;} table.content {font-size:10pt;background-color:#F3F3F3;width:80% }  table.contentG {font-size:11pt;background-color:#FFCCFF;width:80% } table.contentN {font-size:10pt;background-color:#CCCCCC;width:80% } table.footer {font-size:10pt; background-color:#669900;width:80%} P {text-align:left;color:red} .A {width:100%;height:10;text-align:center} .B {text-align:center } .C {color:#AC0000} .D{color:#339933} .E {width:150} .F {font-weight:bold,color:#FF0000} a:visited{color:blue;text-decoration:none} a:link{text-decoration:none} .H {font-size:11pt;background-color:#669900;}</style>
<html><body><table class=header align='center'><tr><td class=A><a class=F>和黄商贸报障中心 - 月报表</a></td></tr></table>";

    $contents.="<table class=content align='center' ><tr><td align=center><table width=300>";

    # 获取上月支持工程师报障处理数量

    my $sqldate=" date_format(t.create_time,'\%m')-0=if(date_format(curdate(),'\%m')-1=0,12,date_format(curdate(),'\%m')-1)";
    #my $sqldate=" 1=1 ";
 
    my $sqlstring = <<"___SQLstring___";
SELECT u.first_name as name,count(t.id) as total FROM users u left join ticket t on (u.id=t.user_id and t.queue_id not in (3,9,10,12,13)) 
where $sqldate  
group by u.first_name order by count(t.id) desc,u.first_name desc
___SQLstring___

    my $sqr = $dbh->prepare($sqlstring);

    $sqr->execute();

    $contents.="<tr><th COLSPAN=3 class=H >上月报障处理情况</th></tr>";
    $contents.="<tr><th><div align=right>支持工程师</div></th><th>&nbsp;</th><th><div align=left>处理报障数量</div></th></tr>";
    while(my $ref = $sqr->fetchrow_hashref()) {

	$contents.="<tr><td><div align=right>$ref->{'name'}</div></td><td>&nbsp;</td><td><div align=left>$ref->{'total'}</div></td></tr>";

    }
    $contents.="</table></td></tr></table>";

    # 获取上月报障处理情况 - 赞扬
    $contents.="<table class=content align='center'><tr><td align=center><table class=contentG >";
    $sqr = $dbh->prepare("SELECT u.first_name as name,count(1) as total  FROM ticket t left join users u  on t.user_id=u.id left join customer_user cu on cu.customer_id=t.customer_user_id  where  t.queue_id=12 and $sqldate group by u.first_name");

    $sqr->execute();

    $contents.="<tr><th COLSPAN=2 class=H>   光 荣 榜 </th></tr>";
    $contents.="<tr><th>工程师</th><th>被赞扬次数</th></tr>";
    while(my $ref = $sqr->fetchrow_hashref()) {

	$contents.="<tr><td class=B><strong>$ref->{'name'}</strong></td><td class=B>$ref->{'total'}</td></tr>";
    }

    $contents.="</table></td></tr></table>";

    # 获取上月报障处理情况 - 投诉
    $contents.="<table class=content align='center'><tr><td align=center><table class=contentN >";
    $sqr = $dbh->prepare("SELECT u.first_name as name,count(1) as total  FROM ticket t left join (customer_user cu,users u) on (cu.customer_id=t.customer_user_id and  t.user_id=u.id ) where  t.queue_id=13 and $sqldate group by u.first_name");

    $sqr->execute();

    $contents.="<tr><th COLSPAN=2 class=H>   投 诉 栏 </th></tr>";
    $contents.="<tr><th>工程师</th><th>被投诉次数</th></tr>";
    while(my $ref = $sqr->fetchrow_hashref()) {

	$contents.="<tr><td class=B><div align=left><strong>$ref->{'name'}</strong></div></td><td class=B><div align=left>$ref->{'total'}</div></td></tr>";
    }

    $contents.="</table></td></tr></table>";


    # 获取上月报障处理情况 - 请求类型
    $contents.="<table class=content align='center'><tr><td align=center><table width=300>";
    $sqr = $dbh->prepare("SELECT type.name as name,count(1) as total from ticket t left join ticket_type type on type.id=t.type_id  where t.queue_id<>3 and  $sqldate group by type.name order by count(1) desc");

    $sqr->execute();

    $contents.="<tr><th COLSPAN=2 class=H>上月报障处理情况 - 请求类型</th></tr>";
    $contents.="<tr><th>请求类型</th><th>数量</th></tr>";
    while(my $ref = $sqr->fetchrow_hashref()) {

	$contents.="<tr><td class=B><div align=left>$ref->{'name'}</div></td><td class=B><div align=left>$ref->{'total'}</div></td></tr>";
    }

    $contents.="</table></td></tr></table>";

    
    # 获取上月报障处理情况 - 地区
    $contents.="<table class=content align='center'><tr><td align=center><table width=300>";
    $sqr = $dbh->prepare("SELECT IFNULL(u.city,'未知') as name,count(1) as total from ticket t left join customer_user u on u.customer_id=t.customer_user_id where t.queue_id<>3 and  $sqldate group by u.city order by count(1) desc");

    $sqr->execute();

    $contents.="<tr><th COLSPAN=2 class=H>上月报障处理情况 - 城市</th></tr>";
    $contents.="<tr><th>城市</th><th>数量</th></tr>";
    while(my $ref = $sqr->fetchrow_hashref()) {

	$contents.="<tr><td class=B>$ref->{'name'}</td><td class=B><div align=left>$ref->{'total'}</div></td></tr>";
    }

    $contents.="</table></td></tr></table>";

    # 获取上月报障处理情况 - 实际处理时间

    $sqlstring = <<"___SQLstring___";
select '5分钟内处理完毕' as timer,count(1) as total from (
SELECT t.tn as tn,ifnull(sum(time.time_unit),0) as timer from ticket t left join time_accounting time on time.ticket_id=t.id where t.queue_id<>3 and   $sqldate   group by t.tn
) as a where a.timer<=5

union all

select '5~30分钟',count(1) from (
SELECT t.tn as tn,ifnull(sum(time.time_unit),0) as timer from ticket t left join time_accounting time on time.ticket_id=t.id where t.queue_id<>3 and  $sqldate  group by t.tn
) as b where b.timer<=30 and b.timer>5

union all

select '30~60分钟',count(1) from (
SELECT t.tn as tn,ifnull(sum(time.time_unit),0) as timer from ticket t left join time_accounting time on time.ticket_id=t.id where t.queue_id<>3 and  $sqldate  group by t.tn
) as c where c.timer<=60 and c.timer>30

union all 

select '60分钟~24小时',count(1) from (
SELECT t.tn as tn,ifnull(sum(time.time_unit),0) as timer from ticket t left join time_accounting time on time.ticket_id=t.id  where t.queue_id<>3 and  $sqldate group by t.tn
) as d where d.timer<=480 and d.timer>60

union all


select '1天以上',count(1) from (
SELECT t.tn as tn,ifnull(sum(time.time_unit),0) as timer from ticket t left join time_accounting time on time.ticket_id=t.id  where t.queue_id<>3 and  $sqldate group by t.tn
) as e where e.timer>480

union all

select '未处理',count(1) from (
SELECT t.tn as tn,ifnull(sum(time.time_unit),0) as timer from ticket t left join time_accounting time on time.ticket_id=t.id  where t.queue_id<>3 and t.ticket_state_id not in (2,3,9)  group by t.tn
) as e 

___SQLstring___

    $contents.="<table class=content align='center'><tr><td align=center><table width=300>";
    $sqr = $dbh->prepare($sqlstring);

    $sqr->execute();

    $contents.="<tr><th COLSPAN=2 class=H>上月报障处理情况 - 实际处理时间</th></tr>";
    $contents.="<tr><th>实际处理时间</th><th>数量</th></tr>";
    while(my $ref = $sqr->fetchrow_hashref()) {

	$contents.="<tr><td class=B>$ref->{'timer'}</td><td class=B><div align=left>$ref->{'total'}</div></td></tr>";
    }

    $contents.="</table></td></tr></table>";

    # 获取IT助手邮件地址

    $sqr = $dbh->prepare("select u.first_name as name,up.preferences_value as email from users u,user_preferences up where u.id=up.user_id and up.preferences_key='UserEmail' and u.first_name like 'IT%'");

    $sqr->execute();

    my $to = 'EBIS <toebis@hwccl.com>';

     while(my $ref = $sqr->fetchrow_hashref()) {

	$to.=",$ref->{name} <$ref->{email}>";
    }

    $dbh->disconnect();

    

    my $footer = <<"___FOOT___";
<table class=content align='center'>
<tr></tr><tr></tr><tr></tr><tr><td class=D> 和黄商贸办公网站入口 : <a href="my.hwccl.com">my.hwccl.com</a></td></tr>
<tr></tr><tr><td class=D>计算机/网络故障处理方法： 1.联系报障中心：<a href="mailto:it\@hwccl.com">it\@hwccl.com</a> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.IMO-在搜索栏输入： it </td></tr>
<tr><td> </td></tr><tr></tr><tr></tr>

<tr><td class=D>
IT技术服务工程师：<br>
Suan: 020-86266855/6833-291<br>
Landy: 020-86266855/6833-324<br>
Marco:020-86266855/6833-326<br>
Homy: 020-86266855/6833-235
</td></tr></table>
<table class=footer align='center'><tr><td class=B>版权所有：<font color=#FF0000>和记黄埔（中国）商贸有限公司</font></td></tr></table></body></html>
___FOOT___

    $contents.=$footer;
    $to='Meanson <meansonw@hwccl.com>,王明柱 <meansonw@gmail.com>';
    
our %db = (
html => $contents,
to => $to
);
